﻿------------------------------------------------------------------------------------------------------------------------
-- <copyright>Copyright 2008-2011 Andreas Huber Doenni</copyright>
-- Distributed under the Microsoft Public License (Ms-PL).
-- See accompanying file License.txt or copy at http://mews.codeplex.com/license.
------------------------------------------------------------------------------------------------------------------------

CREATE TABLE Host(
    Id INT IDENTITY(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY,
    [Name] NVARCHAR(254) NOT NULL,
    Port INT NOT NULL,
    UserName NVARCHAR(64),
    EmailAddress NVARCHAR(320),
    AccountName VARBINARY(256),
    Password VARBINARY(256))
GO
CREATE UNIQUE INDEX NamePort ON Host([Name], Port)
GO

------------------------------------------------------------------------------------------------------------------------

CREATE TABLE OutboundArticle(
    Id INT IDENTITY(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY,
    HostId INT NOT NULL REFERENCES Host(Id),
    DateUtc DATETIME,
    Subject NVARCHAR(128),
    SendStatus INT NOT NULL,
    IsDeleted BIT DEFAULT 0 NOT NULL)
GO
CREATE INDEX IsDeletedSendStatusDateUtc ON OutboundArticle(IsDeleted, SendStatus, DateUtc DESC)
GO
CREATE INDEX HostIdIsDeletedSendStatusDateUtc ON OutboundArticle(HostId, IsDeleted, SendStatus, DateUtc)
GO

------------------------------------------------------------------------------------------------------------------------

CREATE TABLE [Group](
    Id INT IDENTITY(1, 1) CONSTRAINT PrimaryKey PRIMARY KEY,
    HostId INT NOT NULL REFERENCES Host(Id) ON DELETE CASCADE,
    [Name] NVARCHAR(254) NOT NULL,
    DisplayName NVARCHAR(32) NOT NULL,
    NewInboundArticlesReceiveLimit INT NOT NULL,
    CachedInboundArticlesLimit INT NOT NULL,
    SelectedInboundArticleNumber BIGINT,
    IsOriginalInboundArticleBeingWatchedFilter BIT,
    HasOriginalInboundArticleBeenReadFilter BIT,
    IsFollowupInboundArticleBeingWatchedFilter BIT,
    HasFollowupInboundArticleBeenReadFilter BIT)
GO
CREATE UNIQUE INDEX HostIdName ON [Group](HostId, [Name])
GO

------------------------------------------------------------------------------------------------------------------------

-- For inserts and updates it would probably be more efficient to have a single byte column instead of the bit columns.
-- However, that would preclude the definition of indexes including individual bit columns.
CREATE TABLE InboundArticle(
    GroupId INT NOT NULL REFERENCES [Group](Id),
    Number BIGINT NOT NULL,
    ReferencesNumber BIGINT,
    OriginalNumber BIGINT NOT NULL,
    DateUtc DATETIME,
    [From] NVARCHAR(128),
    Subject NVARCHAR(128),
    HasHeaderBeenReceived BIT NOT NULL,
    HasBodyBeenReceived BIT NOT NULL,
    ReceiveFullArticle BIT NOT NULL,
    HasAttachments BIT,
    IsBeingWatched BIT NOT NULL,
    IsExpanded BIT DEFAULT 0 NOT NULL,
    HasBeenRead BIT DEFAULT 0 NOT NULL)
GO
ALTER TABLE InboundArticle ADD CONSTRAINT PrimaryKey PRIMARY KEY(GroupId, Number)
GO
-- The following prevents cascading deletes from Group to InboundArticle. However, since we must also delete the
-- associated article file when we delete an InboundArticle row, we need to have special code in place anyway.
ALTER TABLE InboundArticle ADD FOREIGN KEY(GroupId, ReferencesNumber) REFERENCES InboundArticle(GroupId, Number)
GO
-- In theory we'd also want to add the following constraint, but that keeps us from ever deleting root articles.
-- ALTER TABLE InboundArticle ADD FOREIGN KEY(GroupId, OriginalNumber) REFERENCES InboundArticle(GroupId, Number)
-- GO
CREATE INDEX GroupIdReferencesNumber ON InboundArticle(GroupId, ReferencesNumber)
GO
CREATE INDEX GroupIdReceiveFullArticle ON InboundArticle(GroupId, ReceiveFullArticle)
GO
CREATE INDEX GroupIdIsBeingWatchedHasBeenRead ON InboundArticle(GroupId, IsBeingWatched, HasBeenRead)
GO
CREATE INDEX GroupIdHasBeenRead ON InboundArticle(GroupId, HasBeenRead)
GO
ALTER TABLE [Group] ADD FOREIGN KEY(Id, SelectedInboundArticleNumber) REFERENCES InboundArticle(GroupId, Number)
GO

------------------------------------------------------------------------------------------------------------------------

CREATE TABLE MessageId(
    GroupId INT NOT NULL,
    Number BIGINT NOT NULL,
    MessageId NVARCHAR(254) NOT NULL)
GO
ALTER TABLE MessageId ADD CONSTRAINT PrimaryKey PRIMARY KEY(GroupId, Number)
GO
ALTER TABLE MessageId ADD FOREIGN KEY(GroupId, Number) REFERENCES InboundArticle(GroupId, Number) ON DELETE CASCADE
GO
CREATE UNIQUE INDEX GroupIdMessageId ON MessageId(GroupId, MessageId)
GO

------------------------------------------------------------------------------------------------------------------------

CREATE TABLE MetaData(
    Version INT NOT NULL,
    InsertsAndDeletesSinceLastCompaction INT NOT NULL)
GO
INSERT INTO MetaData VALUES(0, 0)
